package GoodsPackage;

import Util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Statement stmt = null;
    public List<Good> getGoodsByCategory(String category) {
        List<Good> goodsList = new ArrayList<>();
        String sql = "SELECT * FROM goods WHERE category = ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {

            stmt.setString(1, category); // 设置SQL查询中的参数
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Good good = new Good();
                good.setGoods_id(rs.getString("goods_id"));
                good.setGoods_name(rs.getString("goods_name"));
                good.setGoods_category(rs.getString("category"));
                good.setUnitprice(rs.getBigDecimal("unitprice").toString());
                good.setPic(rs.getString("photo"));
                good.setDetails(rs.getString("details"));
                // 设置其他需要的字段
                goodsList.add(good);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    public int deleteGoodByGoodid(String goodsId) {
        PreparedStatement ps = null;

        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "DELETE FROM goods WHERE goods_id=?";
            ps = con.prepareStatement(sql);

            ps.setString(1, goodsId);

            // Execute the delete
            int rowsDeleted = ps.executeUpdate();
            System.out.println(ps);
            System.out.println(goodsId);

            return rowsDeleted;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    public Good queryGoodByGoodid(String goods_id) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "SELECT * FROM goods WHERE goods_id=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, goods_id);

            // Execute the query
            rs = ps.executeQuery();
            System.out.println(goods_id);
            if (rs.next()) {
                // Create a User object with retrieved information
                Good good = new Good();
                good.setGoods_id(rs.getString("goods_id"));
                good.setGoods_name(rs.getString("goods_name"));
                good.setGoods_category(rs.getString("category"));
                good.setDetails(rs.getString("details"));
                good.setStock(rs.getInt("stock"));
                good.setFactory(rs.getString("factory"));
                good.setPic(rs.getString("photo"));
                good.setUnitprice(rs.getString("unitprice"));
                // Set other user information as needed
                return good;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null; // Return null if the user is not found or an error occurs
    }

    public int updateGoodByGoodid(String goodsId, String goodsName, String category, String unitprice, int stock, String factory, String photo, String details) {
        PreparedStatement ps = null;

        try {
            con = DBUtil.getConnection();
            // Use PreparedStatement to prevent SQL injection
            String sql = "UPDATE goods SET goods_name=?, category=?, unitprice=?, stock=?, factory=?, photo=? ,details=? WHERE goods_id=?";
            ps = con.prepareStatement(sql);

            ps.setString(1, goodsName);
            ps.setString(2, category);
            ps.setString(3, unitprice);
            ps.setInt(4, stock);
            ps.setString(5, factory);
            ps.setString(6, photo);
            ps.setString(7, details);
            ps.setString(8, goodsId);

            // Execute the update
            int rowsUpdated = ps.executeUpdate();
            System.out.println(ps);
            System.out.println(goodsId);

            return rowsUpdated;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public int addGood(String goodsId, String goodsName, String category, String unitprice, int stock, String factory, String photo, String details) {
        PreparedStatement ps = null;
        int rowsInserted = 0;

        try {
            // Get database connection
            con = DBUtil.getConnection();

            // SQL statement for inserting new goods
            String sql = "INSERT INTO goods (goods_id, goods_name, category, unitprice, stock, factory, photo, details) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            ps = con.prepareStatement(sql);

            // Set parameters for the PreparedStatement
            ps.setString(1, goodsId);
            ps.setString(2, goodsName);
            ps.setString(3, category);
            ps.setString(4, unitprice);
            ps.setInt(5, stock);
            ps.setString(6, factory);
            ps.setString(7, photo);
            ps.setString(8, details);

            // Execute the insert operation
            rowsInserted = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return rowsInserted;
    }

    public List<Good> searchGood(String searchTerm) {
        List<Good> goodsList = new ArrayList<>();
        String sql = "SELECT * FROM goods WHERE goods_name LIKE ? OR category LIKE ?";

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {

            String likeTerm = "%" + searchTerm + "%";
            stmt.setString(1, likeTerm); // goods_name
            stmt.setString(2, likeTerm); // category

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Good good = new Good();
                good.setGoods_id(rs.getString("goods_id"));
                good.setGoods_name(rs.getString("goods_name"));
                good.setGoods_category(rs.getString("category"));
                good.setUnitprice(rs.getBigDecimal("unitprice").toString());
                good.setPic(rs.getString("photo"));
                good.setDetails(rs.getString("details"));
                goodsList.add(good);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return goodsList;
    }

}
